﻿using B.Z.BasicData.API.Read.DTO.ExcuseDtos;
using B.Z.BasicData.Domain.Entity.Excused;
using B.Z.BasicData.Infrastructure;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Net.Mime;

namespace B.Z.BasicData.API.Read.Controllers
{
    /// <summary>
    /// 控制器(表格文件)
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class ExcelFileController : ControllerBase
    {
        private readonly IBaseRepository<T_Excused> excuseRepository;

        public ExcelFileController(IBaseRepository<T_Excused> excuseRepository)
        {
            this.excuseRepository = excuseRepository;
        }
        /// <summary>
        /// 导出Excel(本年度请假总工时报表)
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult UpDownExcel() 
        {
            XSSFWorkbook book = new XSSFWorkbook();
            ISheet sheet = book.CreateSheet("本年度请假总工时报表");
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("名称/请假月份");
            row.CreateCell(1).SetCellValue("一月");
            row.CreateCell(2).SetCellValue("二月");
            row.CreateCell(3).SetCellValue("三月");
            row.CreateCell(4).SetCellValue("四月");
            row.CreateCell(5).SetCellValue("五月");
            row.CreateCell(6).SetCellValue("六月");
            row.CreateCell(7).SetCellValue("七月");
            row.CreateCell(8).SetCellValue("八月");
            row.CreateCell(9).SetCellValue("九月");
            row.CreateCell(10).SetCellValue("十月");
            row.CreateCell(11).SetCellValue("十一月");
            row.CreateCell(12).SetCellValue("十二月");

            var list = (from a in excuseRepository.GetAll().Where(x => x.Status == 1 && x.Remark == 1)
                       group a by a.Name into Form
                       select new ExcusedFormDtos
                       {
                           UserName = Form.Key,
                           Jan = Form.Where(x => x.StartDateTime.Month == 1 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Feb = Form.Where(x => x.StartDateTime.Month == 2 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Mar = Form.Where(x => x.StartDateTime.Month == 3 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Apr = Form.Where(x => x.StartDateTime.Month == 4 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           May = Form.Where(x => x.StartDateTime.Month == 5 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Jun = Form.Where(x => x.StartDateTime.Month == 6 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Jul = Form.Where(x => x.StartDateTime.Month == 7 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Aug = Form.Where(x => x.StartDateTime.Month == 8 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Sept = Form.Where(x => x.StartDateTime.Month == 9 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Oct = Form.Where(x => x.StartDateTime.Month == 10 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Nov = Form.Where(x => x.StartDateTime.Month == 11 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                           Dec = Form.Where(x => x.StartDateTime.Month == 12 && x.EndDateTime.Year == DateTime.Now.Year).Sum(x => x.TotalHour * 8),
                       }).ToList();
            int num = 1;
            foreach (var item in list)
            {
                IRow rows = sheet.CreateRow(num);
                rows.CreateCell(0).SetCellValue(item.UserName);
                rows.CreateCell(1).SetCellValue(item.Jan);
                rows.CreateCell(2).SetCellValue(item.Feb);
                rows.CreateCell(3).SetCellValue(item.Mar);
                rows.CreateCell(4).SetCellValue(item.Apr);
                rows.CreateCell(5).SetCellValue(item.May);
                rows.CreateCell(6).SetCellValue(item.Jun);
                rows.CreateCell(7).SetCellValue(item.Jul);
                rows.CreateCell(8).SetCellValue(item.Aug);
                rows.CreateCell(9).SetCellValue(item.Sept);
                rows.CreateCell(10).SetCellValue(item.Oct);
                rows.CreateCell(11).SetCellValue(item.Nov);
                rows.CreateCell(12).SetCellValue(item.Dec);
                num++;
            }
            byte[] bt = null;
            using (MemoryStream ms = new MemoryStream()) 
            {
                book.Write(ms);
                bt = ms.ToArray();
            };

            return File(bt,MediaTypeNames.Application.Soap, "本年度请假总工时报表.xlsx");


        }
    }
}
